package LoginServer;

import Library.ErrorCodes;
import Library.Login.*;
import java.io.*;
import java.rmi.RemoteException;
import java.security.MessageDigest;
import java.sql.*;
import java.util.*;

/**
 * Class database maakt verbinding met de database en biedt methodes om data te
 * plaatsen, te verwijderen en aan te passen.
 */
public class Database
{
    /**
     *
     */
    protected Connection m_Connection;

    /**
     * Constructor voor het aanmaken van een Database-klasse
     * @param connectionString connectionstring
     * @param username username
     * @param password wachtwoord
     * @throws SQLException
     * @throws IOException
     */
    public Database(String connectionString, String username, String password) throws SQLException, IOException
    {
        try
        {
            //Class.forName("interbase.interclient.Driver");
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            m_Connection = DriverManager.getConnection(connectionString, username, password);
        }
        catch (Exception ex)
        {
                ex.printStackTrace();
        }
    }

    /**
     * Registeert een ticket voor een login-sessie.
     * @param ticket ticket
     * @return result (zie Library.ErrorCodes)
     */
    public int saveTicket(ITicket ticket)
    {
        int result = ErrorCodes.ERROR_NOERROR;
        try
        {
            String hexValue = ticket.getToken();
            Statement stmt = null;
            try
            {
                m_Connection.setAutoCommit(false);
                stmt = m_Connection.createStatement();
                String username = ticket.getUsername().toUpperCase();
                String sql = "UPDATE ACCOUNT SET Token='" + hexValue + "' WHERE USERNAME = '" + username.toUpperCase() + "'";
                int res = stmt.executeUpdate(sql);
                if (res == 0)
                    result = ErrorCodes.ERROR_CANNOT_REGISTER_TICKET;
                //result = stmt.execute(sql);
                stmt.close();
                m_Connection.commit();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                result = ErrorCodes.ERROR_UNKNOWN;
                ex.printStackTrace();
                try
                {
                    m_Connection.rollback();
                    m_Connection.setAutoCommit(true);
                }
                catch (Exception exc)
                {
                    exc.printStackTrace();
                }
            }
            // Close Statement
            if (stmt != null)
            {
                try
                {
                    if (!stmt.isClosed())
                        stmt.close();
                }
                catch (SQLException exc)
                {
                    exc.printStackTrace();
                }
            }
        }
        catch (RemoteException ex)
        {
            ex.printStackTrace();
        }
        return result;
    }

    /**
     * de nieuwe account wordt in de database opgeslagen
     * @param username
     * @param passwordHash
     * @return result (zie Library.ErrorCodes)
     */
    public int createAccount(String username,  int passwordHash)
    {
        int result = ErrorCodes.ERROR_NOERROR;
        Statement stmt = null;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT USERNAME FROM ACCOUNT WHERE USERNAME = '" + username.toUpperCase() + "'";
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next())
            {
                rs.close();
                result = ErrorCodes.ERROR_ACCOUNT_ALREADY_EXISTS;
            }
            else
            {
                rs.close();

                sql = "INSERT INTO ACCOUNT "
                    + "VALUES('" + username.toUpperCase() + "','" + passwordHash + "',NULL)";

                stmt.execute(sql);
                result = ErrorCodes.ERROR_NOERROR;
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception ex)
        {
            result = ErrorCodes.ERROR_UNKNOWN;
            ex.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception exc)
            {
                exc.printStackTrace();
            }
        }
        // Close Statement
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                    stmt.close();
            }
            catch (SQLException exc)
            {
                exc.printStackTrace();
            }
        }
        return result;
    }

    /**
     * ruimt het geheel op na gebruik
     */
    public void dispose()
    {
        if (m_Connection != null)
        {
            try
            {
                if (!m_Connection.isClosed())
                {
                    m_Connection.close();
                    m_Connection = null;
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
    }

    /**
     * Geeft een map met alle beschikbare accounts uit de database terug
     * @return geeft een map met string/Accounts terug
     */
    public Map<String, Account> getAccounts()
    {
        Map<String, Account> accs = new HashMap<String, Account>();
        String speler;
        int wachtwoord;
        Statement stmt = null;
        boolean failed = false;

        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT * FROM ACCOUNT ORDER BY USERNAME";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                speler = rs.getString("USERNAME").toUpperCase();
                wachtwoord = rs.getInt("PASSWORD");
                accs.put(speler, new Account(speler,wachtwoord));
            }

            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            failed = true;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        return failed ? new HashMap<String, Account>() : accs;
    }

    /**
     * Geeft een map met (Game)Servers
     * @return map met GameServers
     */
    public Map<String, String> getServers()
    {
        Map<String, String> servers = new HashMap<String, String>();
        Statement stmt = null;
        boolean failed = false;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT * FROM SERVER ORDER BY GS_NAME";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                servers.put(rs.getString("GS_NAME").toUpperCase(), rs.getString("IP"));
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            failed = true;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        return failed ? new HashMap<String, String>() : servers;
    }

     /**
     * voegt een gameserver toe aan de lijst in de database en aan
     * LoginServer.m_Gameservers
     * @param GS_NAME
     * @param IP_Port
     * @return result (zie Library.ErrorCodes)
     */
    public int addServer(String GS_NAME, String IP_Port)
    {
        int result = ErrorCodes.ERROR_NOERROR;
        Statement stmt = null;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT * FROM SERVER WHERE GS_NAME = '" + GS_NAME + "'";
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next())
            {
                rs.close();
                result = ErrorCodes.ERROR_SERVER_ALREADY_EXISTS;
            }
            else
            {
                rs.close();
                sql = "INSERT INTO SERVER VALUES('" + GS_NAME + "','" + IP_Port + "')";
                stmt.execute(sql);
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception ex)
        {
            result = ErrorCodes.ERROR_UNKNOWN;
            ex.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception exc)
            {
                exc.printStackTrace();
            }
        }
        // Close Statement
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                    stmt.close();
            }
            catch (SQLException exc)
            {
                exc.printStackTrace();
            }
        }
        return result;
    }

    /**
     * Verwijdert de opgegeven server uit de database en uit
     * LoginServer.m_GameServers
     * @param GS_NAME
     * @return result (zie Library.ErrorCodes)
     */
    public int deleteServer(String GS_NAME)
    {
        int result = ErrorCodes.ERROR_NOERROR;
        Statement stmt = null;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT * FROM SERVER WHERE GS_NAME = '" + GS_NAME + "'";
            ResultSet rs = stmt.executeQuery(sql);
            if (!rs.next())
            {
                rs.close();
                result = ErrorCodes.ERROR_SERVER_NOT_FOUND;
            }
            else
            {
                rs.close();
                sql = "DELETE FROM SERVER WHERE GS_NAME = '" + GS_NAME + "'";
                stmt.execute(sql);
                stmt.close();
                m_Connection.commit();
                m_Connection.setAutoCommit(true);
            }
        }
        catch (Exception ex)
        {
            result = ErrorCodes.ERROR_UNKNOWN;
            ex.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception exc)
            {
                exc.printStackTrace();
            }
        }
        // Close Statement
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                    stmt.close();
            }
            catch (SQLException exc)
            {
                exc.printStackTrace();
            }
        }
        return result;
    }

    /**
     * Verwijdert de opgegeven account uit de database en uit
     * LoginServer.m_Accounts
     * @param Username
     * @return result (zie Library.ErrorCodes)
     */
    public int deleteAccount(String Username)
    {
        Statement stmt = null;
        int result = ErrorCodes.ERROR_NOERROR;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT * FROM ACCOUNT WHERE USERNAME  = '" + Username.toUpperCase() + "'";
            ResultSet rs = stmt.executeQuery(sql);
            if (!rs.next())
            {
                rs.close();
                result = ErrorCodes.ERROR_ACCOUNT_NOT_FOUND;
            }
            else
            {
                rs.close();
                sql = "DELETE FROM ACCOUNT "
                       + "WHERE USERNAME = '" + Username.toUpperCase() + "'";
                stmt.execute(sql);
                stmt.close();
                m_Connection.commit();
                m_Connection.setAutoCommit(true);
            }
        }
        catch (Exception ex)
        {
            result = ErrorCodes.ERROR_UNKNOWN;
            ex.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception exc)
            {
                exc.printStackTrace();
            }
        }

        // Close Statement
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                    stmt.close();
            }
            catch (SQLException exc)
            {
                exc.printStackTrace();
            }
        }
        return result;
    }

    /**
     *
     * @param ticket
     * @return result (zie Library.ErrorCodes)
     */
    public int removeTicket(ITicket ticket)
    {
        int result = ErrorCodes.ERROR_NOERROR;
        try
        {
            String username = ticket.getUsername().toUpperCase();
            Statement stmt = null;
            try
            {
                m_Connection.setAutoCommit(false);
                stmt = m_Connection.createStatement();
                String sql = "UPDATE ACCOUNT SET Token= 'NULL' WHERE USERNAME = '" + username + "'";
                int res = stmt.executeUpdate(sql);
                if (res == 0)
                    result = ErrorCodes.ERROR_TICKET_MISSING;
                stmt.close();
                m_Connection.commit();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                result = ErrorCodes.ERROR_UNKNOWN;
                ex.printStackTrace();
                try
                {
                    m_Connection.rollback();
                    m_Connection.setAutoCommit(true);
                }
                catch (Exception exc)
                {
                    exc.printStackTrace();
                }
            }
            // Close Statement
            if (stmt != null)
            {
                try
                {
                    if (!stmt.isClosed())
                        stmt.close();
                }
                catch (SQLException exc)
                {
                    exc.printStackTrace();
                }
            }
        }
        catch (RemoteException ex)
        {
            ex.printStackTrace();
        }
        return result;
    }

    /**
     *
     * @return
     */
    public Map<String, String> getTickets()
    {
        Map<String, String> tickets = new HashMap<String, String>();
        Statement stmt = null;
        boolean failed = false;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT * FROM ACCOUNT WHERE TOKEN <> 'NULL'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                tickets.put(rs.getString("USERNAME").toUpperCase(), rs.getString("TOKEN"));
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            failed = true;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        return failed ? new HashMap<String, String>() : tickets;
    }

    /**
     *
     * @return
     */
    public int removeAllTickets()
    {
        int result = ErrorCodes.ERROR_NOERROR;
        try
        {

            Statement stmt = null;
            int res = -1;
            try
            {
                m_Connection.setAutoCommit(false);
                stmt = m_Connection.createStatement();
                //String sql = "SELECT * FROM ACCOUNT WHERE TOKEN <> 'NULL'";
                String sqlRemove = "UPDATE ACCOUNT SET Token= 'NULL'";
                //ResultSet rs = stmt.executeQuery(sql);
                //while (rs.next())
                //{
                    res = stmt.executeUpdate(sqlRemove);
                //}

                if (res == 0)
                    result = ErrorCodes.ERROR_TICKET_MISSING;
                stmt.close();
                m_Connection.commit();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                result = ErrorCodes.ERROR_UNKNOWN;
                ex.printStackTrace();
                try
                {
                    m_Connection.rollback();
                    m_Connection.setAutoCommit(true);
                }
                catch (Exception exc)
                {
                    exc.printStackTrace();
                }
            }
            // Close Statement
            if (stmt != null)
            {
                try
                {
                    if (!stmt.isClosed())
                        stmt.close();
                }
                catch (SQLException exc)
                {
                    exc.printStackTrace();
                }
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        return result;
    }
}